/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import DTO.DTOInventory;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 *
 * @author Pham
 */
public class DALInventory {
    ConnectionDB conn;
    
    public DALInventory() {
        conn = new ConnectionDB();
    }
    
    //method get quantity of a product on a time
    public DTOInventory getQuantityofInventory(int proId, Date date)
    {
        try{
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String dateString = df.format(date);
            String query = "select * from inventory i, product p where i.product_id = p.product_id and i.product_id = " + proId + " and "
                + "inventory_dateupdate <= '" + dateString + "' order by inventory_dateupdate DESC limit 1";
            DTOInventory dtoInv = null;
            //System.out.printf(query);
            ResultSet rs = conn.executeQuery(query);
            while(rs.next())
            {
                dtoInv = new DTOInventory(rs);
            }
            
            return dtoInv;
        }
        catch(SQLException ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
        
    }
    
    //method insert new row to inventory
    public boolean insertRowInventory(DTOInventory dtoInv)
    {
        try{
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String date = df.format(dtoInv.getInventoryDate());
            String queryString = "";
//            DTOInventory dtoCurrentInv = getCurrentInventory(dtoInv.getDtoPro().getProductID());
//            if(dtoCurrentInv != null){
//                int newQuantity = dtoCurrentInv.getInventoryQuatity() + dtoInv.getInventoryQuatity();
//                dtoInv.setInventoryQuatity(newQuantity);
//                queryString = String.format("insert into inventory(inventory_dateupdate, inventory_quantity, product_id) "
//                    + "values('%s', %d, %d)", date, newQuantity, dtoInv.getDtoPro().getProductID());
//            }
//            else
                queryString = String.format("insert into inventory(inventory_dateupdate, inventory_quantity, product_id) "
                    + "values('%s', %d, %d)", date, dtoInv.getInventoryQuatity(), dtoInv.getDtoPro().getProductID());
            return conn.exeUpdate(queryString);
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return false;
        }
    }
    
    //method get current quantity in inventory
    public DTOInventory getCurrentInventory(int proId)
    {
        try
        {
            
            String query = "select * from inventory i, product p where i.product_id = p.product_id and i.product_id = " + proId + ""
                + " order by inventory_dateupdate DESC limit 1";
            DTOInventory dtoInv = null;
            //System.out.printf(query);
            ResultSet rs = conn.executeQuery(query);
            while(rs.next())
            {
                dtoInv = new DTOInventory(rs);
            }
            
            return dtoInv;
        }
        catch(SQLException ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
}
